﻿using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApp1
{
    public class Clauses
    {
        private StringBuilder Builder { get; set; }
        public Clauses()
        {
            Builder = new StringBuilder();
        }

        private void Append(string value, string pad = " ", string suf = " ")
        {
            Builder.Append($"{pad}{value}{suf}");
        }

        private void Append(bool exp, string value, string pad = " ", string suf = " ")
        {
            if (exp)
            {
                Append(value, pad, suf);
            }

        }

        public Clauses And(string con)
        {
            Append($"and {con}");
            return this;
        }

        public Clauses And(bool exp, string con)
        {
            Append(exp, con);
            return this;
        }

        public Clauses AndIsNotNullOrWhiteSpace(string con)
        {
            And(!string.IsNullOrWhiteSpace(con), con);
            return this;
        }

        public Clauses AndIsNotNullOrWhiteSpace(string value, string con)
        {
            And(!string.IsNullOrWhiteSpace(value), con);
            return this;
        }

        public Clauses Or(string con)
        {
            Append($"or {con}");
            return this;
        }

        public Clauses Or(bool exp, string con)
        {
            Append(exp, $"or {con}");
            return this;
        }

        public Clauses OrIsNotNullOrWhiteSpace(string con)
        {
            Or(!string.IsNullOrWhiteSpace(con), con);
            return this;
        }

        public Clauses OrIsNotNullOrWhiteSpace(string value, string con)
        {
            Or(!string.IsNullOrWhiteSpace(value), con);
            return this;
        }

        override
        public string ToString()
        {
            var str = Builder.ToString();
            return string.IsNullOrWhiteSpace(str) ? "" : $" where {str}";
        }
    }

    public class Sql
    {
        // select * from xxx where order by 
        private string table = "";
        private List<string> order = null;
        private string group = "";
        private string where = "";
        private string select = "*";
        private List<string> join = null;

        public Sql Select(string columns = "*")
        {
            select = columns;
            return this;
        }

        public Sql Select(string[] columns = null)
        {
            select = columns == null ? "*" : string.Join(',', columns);
            return this;
        }

        public Sql Select(List<string> columns = null)
        {
            select = columns == null ? "*" : string.Join(',', columns);
            return this;
        }

        public Sql Where(string condiction, bool enable = true)
        {
            if (enable)
            {
                where = condiction.ToString();
            }
            return this;
        }

        public Sql Where(Sql condiction, bool enable = true)
        {
            if (enable)
            {
                where = condiction.ToString();
            }
            return this;
        }

        public Sql Order(string order, bool desc = false)
        {
            var orderStr = !desc ? order : $"{order} desc";
            if (this.order == null)
            {
                this.order = new List<string>() { orderStr };
            }
            else
            {
                this.order.Add(orderStr);
            }
            return this;
        }

        public Sql Group(string group)
        {
            this.group = group;
            return this;
        }

        public Sql Group(string[] groups)
        {
            group = string.Join(',', groups);
            return this;
        }

        public Sql Group(List<string> groups)
        {
            group = string.Join(',', groups);
            return this;
        }

        public Sql From(string table, string alias = "")
        {
            this.table = table;
            if (!string.IsNullOrEmpty(alias))
            {
                this.table += $"({table}) {alias}";
            }
            return this;
        }

        public Sql From(Sql sql, string alias = "")
        {
            var table = sql.ToString();
            if (!string.IsNullOrEmpty(alias))
            {
                this.table += $"({table}) {alias}";
            }
            return this;
        }

        public Sql InnerJoin(string table, string condiction)
        {
            return Join("inner", table, condiction); ;

        }

        public Sql LeftJoin(string table, string condiction)
        {
            return Join("left", table, condiction); ;
        }

        public Sql Join(string type, string table, string condiction)
        {
            var joinStr = $"{type} join {table} on {condiction}";
            if (join == null)
            {
                join = new List<string>() { joinStr };
            }
            else
            {
                join.Add(joinStr);
            }
            return this;
        }

        public Sql RightJoin(string table, string condiction)
        {
            return Join("right", table, condiction);
        }

        override
        public string ToString()
        {
            return $@"select {select} 
from {table} 
{(string.IsNullOrEmpty(where) ? "" : $"where {where}")} 
{(join == null ? "" : $"{string.Join(',', join)}")} 
{(order == null ? "" : $"order by {string.Join(',', order)}")} 
{(string.IsNullOrEmpty(group) ? "" : $"group by {group}")}";
        }

        public static Sql Create()
        {
            return new Sql();
        }
    }
}
